﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class ResourceRequest
    {
        //exposed attributes
        public int BookingID { get; set; }
        public String ResName { get; set; }
        public String Requester { get; set; }
        public int Quantity { get; set; }
        public Boolean Approved { get; set; }
        public DateTime createdTimeStamp { get; set; }

        //internal class variables/arrays
        protected String[] attribNames;
        private int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public ResourceRequest()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            //Initialise with sensible defaults
            BookingID = -1;
            ResName = "";
            Requester = "";
            Quantity = 1;
            Approved = false;
            createdTimeStamp = Convert.ToDateTime(System.DateTime.Now.ToString());

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later

        }

        public int Load(int bookID, String resName)
        {
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + bookID + " AND " + attribNames[1] + "='" + resName + "'"; //strings that'll handle query.

            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];

            NewOrOld = false;   //importing in a new record

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //verify input keys
            if (bookID < 0) { return -1; }
            if (resName == "" || resName == "*" || resName == " ") { return -1; }

            queryFull += queryCondition;

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if it's a primary key, only one result should be returned. Error if not the case
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //import values from DB into class
            try { BookingID = Convert.ToInt32(outputVars[0]); }
            catch { return -1; }

            ResName = outputVars[1];
            Requester = outputVars[2];

            try { Quantity = Convert.ToInt32(outputVars[3]); }
            catch { return -1; }

            try { Approved = Convert.ToBoolean(outputVars[4]); }
            catch { return -1; }

            try { createdTimeStamp = DateTime.Parse(outputVars[5]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";
            int result = 0;

            //verify input keys
            if (BookingID < 0) { return -1; }

            /* TO-DO: UNCOMMENT ONCE BOOKING CLASS EXISTS */
            //Booking test = new Booking();
            //result = test.Load(BookingID);

            if (ResName == "" || ResName == "*" || ResName == " ") { return -1; }
            ResourceRecord test = new ResourceRecord();
            result = test.Load(ResName);
            if (result < 0) { return -1; }

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " VALUES (";
                queryFull += BookingID + ", ";
                queryFull += "'" + ResName + "', ";
                queryFull += "'" + Requester + "', ";
                queryFull += Quantity + ", ";
                queryFull += "'" + Convert.ToString(Approved) + "', ";

                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                ResourceRequest testA = new ResourceRequest();
                int initCheck = testA.Load(BookingID, ResName);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query (the two primary key fields are skipped here)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + ResName + "'"; //compound key!
                queryFull += attribNames[2] + "='" + Requester + "', ";
                queryFull += attribNames[3] + "=" + Quantity + ", ";
                queryFull += attribNames[4] + "='" + Convert.ToString(Approved) + "', ";

                queryFull += attribNames[5] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            //if new record, verify it saved correctly
            if (NewOrOld == true)
            {
                int saveCheck = Load(BookingID, ResName);
                if (saveCheck < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe
            return 0;
        }

        public int Delete()
        {
            //Use with caution! This will fail if orphaned entries exist
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + ResName + "'";

            //verify input keys
            if (BookingID < 0) { return -1; }
            if (ResName == "" || ResName == "*" || ResName == " ") { return -1; }

            //Check record exists in the first place
            ResourceRequest test = new ResourceRequest();
            int initCheck = test.Load(BookingID, ResName);
            if (initCheck < 0) { return -1; }

            //Wot? No DB integrity checks? Resource Requests don't need them, as deleting won't result in orphan entries 

            queryFull += queryCondition; //pull query together

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Check was deleted successfully
            int delCheck = Load(BookingID, ResName);
            if (delCheck > -1) { return -1; }

            NewOrOld = true;    //as the record no longer exists in the database, is considered 'new'

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 5;
            tableName = "tblResourceRequests";
            attribNames = new String[numAttribs + 1];
            attribNames[0] = "intBookingID"; //primary key value
            attribNames[1] = "txtResource";
            attribNames[2] = "txtUserName";
            attribNames[3] = "intRequestQuantity";
            attribNames[4] = "bitApproved";
            attribNames[5] = "dtTimeStamp";
        }
    }
}